import pymysql
import requests


class Eccang:
    def __init__(self):
        self.headers1 = {  # Bearer -ictSN4Lgii5oQVvAkJs7kNJ1sxc4C3u
            'Authorization': 'Bearer -ictSN4Lgii5oQVvAkJs7kNJ1sxc4C3u',
            'Referer': 'http://home.eccang.com/',
            'sec-ch-ua': '"Chromium";v="92", " Not A;Brand";v="99", "Google Chrome";v="92"',
            'sec-ch-ua-mobile': '?0',
            'Sec-Fetch-Dest': 'empty',
            'Sec-Fetch-Mode': 'cors',
            'Sec-Fetch-Site': 'cross-site',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36',
        }
        self.conn = pymysql.connect(host='192.168.2.205', port=3306, user='jinhuitao',
                                    password='jinhuitao_123456', database='sunway_db', charset='utf8')
        self.cursor = self.conn.cursor()  # 创建游标

    def time(self):
        pass

    def sql(self, user_account, img_url, asin, sku, campaign_name, ad_group_name, aapr_status, impressions, clicks, ctr,
            cpc, cost,
            conversions7d, sales7d, acos7d, complex_acos, complex_asoas, cpa,
            conversion7d_rate, roas, ad_order_pro, direct_sales, indirect_sales, direct_orders,
            indirect_orders, direct_sales_pro, direct_orders_pro, natural_orders_sku, natural_sales_sku, created_time,
            updated_time):  # acoas, asoas,
        sql = 'insert into ecang_chanpin(`user_account`,`img_url`, `asin`, `sku`, `campaign_name`, `ad_group_name`, `aapr_status`, `impressions`, `clicks`, `ctr`, `cpc`, `cost`,`conversions7d`, `sales7d`, `acos7d`, `complex_acos`, `complex_asoas`, `cpa`,`conversion7d_rate`, `roas`, `ad_order_pro`,  `direct_sales`, `indirect_sales`, `direct_orders`,`indirect_orders`, `direct_sales_pro`, `direct_orders_pro`, `natural_orders_sku`, `natural_sales_sku`, `created_time`, `updated_time`) values(%s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s)'
        print('{}插入到数据库中'.format(asin))  # `acoas`, `asoas`,
        self.cursor.execute(sql, (user_account,
                                  img_url, asin, sku, campaign_name, ad_group_name, aapr_status, impressions, clicks,
                                  ctr, cpc, cost,
                                  conversions7d, sales7d, acos7d, complex_acos, complex_asoas, cpa,
                                  conversion7d_rate, roas, ad_order_pro, direct_sales, indirect_sales, direct_orders,
                                  indirect_orders, direct_sales_pro, direct_orders_pro, natural_orders_sku,
                                  natural_sales_sku, created_time,
                                  updated_time))  # acoas, asoas,
        self.conn.commit()
        print('{}提交数据到数据库中'.format(asin))

    def account_number(self):  # 获取每个账号
        url = 'https://app-amz.eccang.com/ad-campaigns/user-accounts'
        response_one = requests.post(url, headers=self.headers1).json()
        for account in response_one["data"]:
            user_account = account['user_account']  # 账号
            currency = account['currency']  # 符号
            data2 = {
                'ad_type': '1',
                'amz_path': "/home/index",
                'currency_code': f"{currency}",
                'generated_date_end': "2021-10-31 23:59:59",  # 结束时间
                'generated_date_start': "2021-10-01 00:00:00",  # 开始时间
                'page': '1',
                'pageSize': '1000',
                'page_type': "ad_product",
                'search_key': "impressions",
                'search_time_type': "create",
                'source': "product",
                'user_account': f"{user_account}",
            }
            print(f'{user_account}开始获取', '*' * 20)
            try:
                self.totalCount(data2, user_account, currency)
            except Exception as g:
                self.totalCount(data2, user_account, currency)
            print('*' * 30)
        self.cursor.close()
        self.conn.close()

    def totalCount(self, data2, user_account,currency):  # 获取每个账号的总数量
        list1 = []
        url2 = 'https://app-amz.eccang.com/ad-product/list'
        response_two = requests.post(url=url2, headers=self.headers1, data=data2).json()

        # print(self.headers1)
        # print(data2)
        # print(response_two)
        pages = response_two["paginator"]["totalCount"]
        pages_int = int(pages / 1000)
        # print(pages)
        # print(pages / 1000)
        if pages / 1000 > 1:
            # print('大于1')
            if pages / 1000 - pages_int > 0:
                pages_num = pages_int + 1
                # print('大于2', pages_num)
                list1.append(pages_num)
            else:
                pages_num = pages_int
                # print('整数',pages_num)
                list1.append(pages_num)
        else:
            pages_num = 1
            # print('小于1',pages_num)
            list1.append(pages_num)

        for i in range(1,list1[0]+1):
            print(f'第{i}页')
            data3 = {
                'ad_type': '1',
                'amz_path': "/home/index",
                'currency_code': f"{currency}",
                'generated_date_end': "2021-11-11 23:59:59",  # 结束时间
                'generated_date_start': "2021-11-11 00:00:00",  # 开始时间
                'page': f"{i}",
                'pageSize': 1000,
                'page_type': "ad_product",
                'search_key': "impressions",
                'search_time_type': "create",
                'source': "product",
                'user_account': f"{user_account}",
            }

            try:
                self.all_data(data3, user_account)
            except Exception as g:
                self.all_data(data3, user_account)

    def all_data(self, data3, user_account):
        url2 = 'https://app-amz.eccang.com/ad-product/list'
        response_three = requests.post(url=url2, headers=self.headers1, data=data3).json()
        # print(response_three)
        for skus in response_three["data"]["list"]:
            # print(sku)
            img_url = skus["img_url"]
            asin = skus["asin"]
            sku = skus["sku"]
            campaign_name = skus["campaign_name"]  # 所在广告活动
            ad_group_name = skus["ad_group_name"]  # 所在广告组
            aapr_status = skus["aapr_status"]  # 状态为paused是已暂停，enabled是进行中
            impressions = skus["impressions"]  # Imp
            clicks = skus["clicks"]  # clicks
            ctr = skus["ctr"]  # ctr
            cpc = skus["cpc"]  # cpc
            cost = skus["cost"]  # Spend
            conversions7d = skus["conversions7d"]  # 广告订单量
            sales7d = skus["sales7d"]  # 广告销售额
            acos7d = skus["acos7d"]  # ACoS
            complex_acos = skus["complex_acos"]  # 综合ACoS
            complex_asoas = skus["complex_asoas"]  # 综合ASOAS
            cpa = skus["cpa"]  # cpa
            conversion7d_rate = skus["conversion7d_rate"]  # CVR
            roas = skus["roas"]  # RoAS
            ad_order_pro = skus["ad_order_pro"]  # 广告订单百分比
            # acoas = skus["acoas"]  # acoas
            # asoas = skus["asoas"]  # asoas
            direct_sales = skus["direct_sales"]  # 直接成交销售额
            indirect_sales = skus["indirect_sales"]  # 间接成交销售额
            direct_orders = skus["direct_orders"]  # 直接成交订单
            indirect_orders = skus["indirect_orders"]  # 间接成交订单
            direct_sales_pro = skus["direct_sales_pro"]  # 直接成交订单占比
            direct_orders_pro = skus["direct_orders_pro"]  # 直接成交销售额占比
            natural_orders_sku = skus["natural_orders_sku"]  # 自然流量订单
            natural_sales_sku = skus["natural_sales_sku"]  # 自然流量订单销售额
            created_time = skus["created_time"]  # 创建时间
            updated_time = skus["updated_time"]  # 更新时间

            all = (
            user_account, img_url, asin, sku, campaign_name, ad_group_name, aapr_status, impressions, clicks, ctr, cpc,
            cost,
            conversions7d, sales7d, acos7d, complex_acos, complex_asoas, cpa,
            conversion7d_rate, roas, ad_order_pro, direct_sales, indirect_sales, direct_orders,
            indirect_orders, direct_sales_pro, direct_orders_pro, natural_orders_sku, natural_sales_sku,
            created_time, updated_time)  # acoas, asoas,
            print(all)
            self.sql(user_account, img_url, asin, sku, campaign_name, ad_group_name, aapr_status, impressions, clicks, ctr,
                     cpc, cost,
                     conversions7d, sales7d, acos7d, complex_acos, complex_asoas, cpa,
                     conversion7d_rate, roas, ad_order_pro, direct_sales, indirect_sales, direct_orders,
                     indirect_orders, direct_sales_pro, direct_orders_pro, natural_orders_sku, natural_sales_sku,
                     created_time, updated_time)  # acoas, asoas,


if __name__ == '__main__':
    run = Eccang()
    run.account_number()
